﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BIExtAuth.Model;
using BIExtAuth.DBUtility;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace BIExtAuth.DAL
{
    /// <summary>
    /// ClassName：用户数据访问类
    /// Author：scc-john
    /// CreateDate：2013-09-22
    /// </summary>
    public class UserDAL
    {
        private DBHelper dbHelper = null;
        public UserDAL()
        {
            string connString = ConfigurationManager.ConnectionStrings["BISYS"].ConnectionString;
            string p = ConfigurationManager.ConnectionStrings["BISYS"].ProviderName;
            Providers prov = ProviderType.GetProviderByName(p);
            this.dbHelper = new DBHelper(connString, prov);
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user">BI用户</param>
        /// <returns>受影响的行数</returns>
        public int AddUser(BIUser user)
        {
            int ret = 0;
            string sql = "usp_AddUser";
            this.dbHelper.AddParameter("@UserID", user.UserID);
            this.dbHelper.AddParameter("@UserName", user.UserName);
            this.dbHelper.AddParameter("@Password", user.UserPwd);
            this.dbHelper.AddParameter("@Description", user.UserDesc);
            this.dbHelper.AddParameter("@Email", user.UserEmail);
            SqlParameter par = new SqlParameter();
            par.ParameterName = "@Status";
            par.SqlDbType = SqlDbType.Bit;
            par.SqlValue = user.UserStatus;
            this.dbHelper.AddParameter(par);
            ret = this.dbHelper.ExecuteNonQuery(sql, CommandType.StoredProcedure);
            return ret;
        }

        /// <summary>
        /// 更新用户
        /// </summary>
        /// <param name="user">BI用户</param>
        /// <returns>受影响的行数</returns>
        public int UpdateUser(BIUser user)
        {
            int ret = 0;
            string sql = "usp_UpdateUser";
            this.dbHelper.AddParameter("@UserID", user.UserID);
            this.dbHelper.AddParameter("@UserName", user.UserName);
            this.dbHelper.AddParameter("@Description", user.UserDesc);
            this.dbHelper.AddParameter("@Email", user.UserEmail);
            SqlParameter par = new SqlParameter();
            par.ParameterName = "@Status";
            par.SqlDbType = SqlDbType.Bit;
            par.SqlValue = user.UserStatus;
            this.dbHelper.AddParameter(par);
            ret = this.dbHelper.ExecuteNonQuery(sql, CommandType.StoredProcedure);
            return ret;
        }

        /// <summary>
        /// 更新用户密码
        /// </summary>
        /// <param name="user">BI用户</param>
        /// <returns>受影响的行数</returns>
        public int ChangePwd(BIUser user)
        {
            int ret = 0;
            string sql = "usp_UpdateUserPwd";
            this.dbHelper.AddParameter("@Password", user.UserPwd);
            this.dbHelper.AddParameter("@UserID", user.UserID);
            ret = this.dbHelper.ExecuteNonQuery(sql, CommandType.StoredProcedure);
            return ret;
        }

        /// <summary>
        /// 检查用户是否存在并启用
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="password">用户密码</param>
        /// <returns>true OR false</returns>
        public bool CheckUser(string userID, string password)
        {
            object ret = 0;
            string sql = "usp_CheckUser";
            this.dbHelper.AddParameter("@UserID", userID);
            this.dbHelper.AddParameter("@Password", password);
            SqlParameter par = new SqlParameter();
            par.ParameterName = "@Status";
            par.SqlDbType = SqlDbType.Bit;
            par.SqlValue = true;
            this.dbHelper.AddParameter(par);
            ret = this.dbHelper.ExecuteScalar(sql, CommandType.StoredProcedure);
            return Convert.ToInt32(ret) == 1 ? true : false;
        }

        /// <summary>
        /// 获取所有的用户
        /// </summary>
        /// <returns>用户集合</returns>
        public DataSet GetAllUsers()
        {
            DataSet ds = new DataSet();
            string sql = "usp_GetAllUser";
            SqlParameter par = new SqlParameter();
            par.ParameterName = "@Status";
            par.SqlDbType = SqlDbType.Bit;
            par.SqlValue = true;
            this.dbHelper.AddParameter(par);
            ds = this.dbHelper.ExecuteDataSet(sql, CommandType.StoredProcedure);
            return ds;
        }

        /// <summary>
        /// 根据用户ID查询用户
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>BI用户</returns>
        public BIUser GetUserByUserID(string userID)
        {
            BIUser user = null;
            string sql = "usp_GetUserByUserID";
            this.dbHelper.AddParameter("@UserID", userID);
            DataSet ds = this.dbHelper.ExecuteDataSet(sql, CommandType.StoredProcedure);
            if (ds.Tables[0].Rows.Count > 0)
            {
                user = new BIUser();
                user.UserID = userID;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    user.UserName = Convert.ToString(dr["UserName"]);
                    user.UserPwd = Convert.ToString(dr["Password"]);
                    user.UserDesc = Convert.ToString(dr["Description"]);
                    user.UserEmail = Convert.ToString(dr["Email"]);
                    user.UserStatus = Convert.ToBoolean(dr["Status"]);
                }
            }
            return user;
        }
    }
}
